Click Here!
home account info subscribe login search My ITKnowledge FAQ/help site map contact us


 
Brief Full
 Advanced
      Search
 Search Tips
To access the contents, click the chapter and section titles.

Oracle Performance Tuning and Optimization
(Publisher: Macmillan Computer Publishing)
Author(s): Edward Whalen
ISBN: 067230886x
Publication Date: 04/01/96

Bookmark It

Search this book:
 
Previous Table of Contents Next


PARALLEL

The PARALLEL hint specifies the desired number of query servers to be used for this specific operation.

The syntax of this hint is as follows:

/*+ PARALLEL ( table [ , degree ] [ , split ] ) */

Alternatively, you can use the PARALLEL hint with the FULL hint:

/*+ FULL( table ) PARALLEL ( table [ , degree ] [ , split ] ) */

Typically, the table and degree parameters are specified.

This hint takes two comma-separated values after the table definition. The first value specifies the degree of parallelism; the second value specifies the split among instances in a parallel server environment. If degree is not specified, a value is chosen by the query coordinator.

NOCACHE

The NOCACHE hint specifies that the blocks retrieved for the table in the hint are placed at the least-recently-used end of the LRU (least recently used) list in the buffer cache when a full-table scan is performed. This is the default behavior in a table scan. The buffer entries are put on the end of the LRU to age more quickly. This is done because most of the data read in a full-table scan is usually discarded. The NOCACHE hint can be useful if very little of the data in a full table scan is used.

The syntax of this hint is as follows:

/*+ NOCACHE ( table ) */

Alternatively, you can use the NOCACHE hint with the FULL hint:

/*+ FULL( table ) NOCACHE ( table ) */

The NOCACHE hint can be useful if you think you will be reading large amounts of data that will unnecessarily be held in the buffer cache.

NOPARALLEL

The NOPARALLEL hint can be used to override the default parallel query operations, even though the default table parameters allow for parallel query processing.

The syntax of this hint is as follows:

/*+ NOPARALLEL ( table ) */

The NOPARALLEL hint can be useful for certain conditions in which you have to reduce the load on the system or reduce I/O contention. This hint essentially disables parallel query processing for this SQL statement.

PUSH_SUBQ

The PUSH_SUBQ hint causes nonmerged subqueries to be evaluated at the earliest possible place in the execution plan. Normally, subqueries that are not merged are executed as the last step in the execution plan. If the subquery is inexpensive and reduces the number of rows significantly, you can improve performance by executing it earlier.

The syntax of this hint is as follows:

/*+ PUSH_SUBQ */

Summary

Because you know more about your data and your application than the Oracle optimizer does, you can make significant improvements to the execution plan of the SQL statements. The Oracle optimizer is very efficient and works quite well to produce the best execution plan for your SQL statements based on the information it has to work with; however, anything you can do to give the optimizer additional information about the execution process will help performance.

By using hints, specific information you know about your data and database can be used to further enhance the performance of certain SQL statements. By using hints, you can enhance specific operations that might otherwise be inefficient. The best way you can significantly improve the performance of your system is by knowing it. Understand the data access patterns. Determine what the users are most likely to access and how they will access it.

By knowing your application and data, you can significantly improve the performance of your server and your application. Hints can help you transfer your knowledge of your data and application to the Oracle optimizer.


Previous Table of Contents Next


Products |  Contact Us |  About Us |  Privacy  |  Ad Info  |  Home

Use of this site is subject to certain Terms & Conditions, Copyright © 1996-2000 EarthWeb Inc.
All rights reserved. Reproduction whole or in part in any form or medium without express written permission of EarthWeb is prohibited.